CREATE PROC Users_Login
@UserName VARCHAR(100),
@Password VARCHAR(100)
AS
BEGIN
	SELECT * FROM Users WHERE LOWER(UserName) = @UserName AND Password = @Password AND IsActive = 1
END
GO

/*Chat proc*/

CREATE PROC ChatRooms_GetByUsers
@FromUserId INT,
@ToUserId INT
AS
BEGIN
	SELECT * FROM ChatRooms WHERE FromUserId = @FromUserId AND ToUserId = @ToUserId
END
GO

CREATE PROC ChatRooms_GetByUserId
@UserId INT
AS
BEGIN
	SELECT * FROM ChatRooms WHERE FromUserId = @UserId OR ToUserId = @UserId
END
GO

CREATE PROC ChatRooms_SearchAll
@FromDate DATETIME,
@ToDate DATETIME,
@UserId INT
AS
BEGIN
	SELECT * FROM ChatRooms WHERE (FromUserId = @UserId OR ToUserId = @UserId) AND CreateDate BETWEEN @FromDate AND @ToDate 
END
GO

CREATE PROC ChatRooms_GetById
@RoomId INT
AS
BEGIN
	SELECT * FROM ChatRooms WHERE Id = @RoomId
END
GO

CREATE PROC ChatRooms_Insert
@FromUserId INT,
@ToUserId INT,
@CreateDate DATETIME
AS
BEGIN
	INSERT INTO [ChatRooms]
           ([FromUserId]
           ,[ToUserId]
           ,[CreateDate])
     VALUES
           (@FromUserId
           ,@ToUserId
           ,@CreateDate)
    SELECT @@IDENTITY
END
GO

CREATE PROC ChatMessages_GetHistoriesByTop
@ChatRoomId INT,
@LastMessageId BIGINT,
@Top INT
AS
BEGIN
	SELECT TOP(@Top) * FROM ChatMessages WHERE RoomId = @ChatRoomId AND @LastMessageId > Id ORDER BY Id DESC 
END
GO

CREATE PROC ChatMessages_GetByRoomId
@ChatRoomId INT
AS
BEGIN
	SELECT * FROM ChatMessages WHERE RoomId = @ChatRoomId
END
GO

CREATE PROC ChatMessages_GetLastestByRoomId
@ChatRoomId INT
AS
BEGIN
	SELECT TOP 1 * FROM ChatMessages WHERE RoomId = @ChatRoomId ORDER BY Id DESC
END
GO

CREATE PROC ChatMessages_GetById
@Id INT
AS
BEGIN
	SELECT  * FROM ChatMessages WHERE Id = @Id
END
GO

CREATE PROC ChatMessages_Insert
@RoomId INT,
@FromUserId INT,
@ToUserId INT,
@Message NVARCHAR(1000),
@CreateDate DATETIME,
@CreateDateStamp BIGINT,
@IsRead BIT,
@Status INT,
@Id BIGINT OUTPUT
AS
BEGIN
	INSERT INTO [TaskManager].[dbo].[ChatMessages]
           ([RoomId]
           ,[Message]
           ,[FromUserId]
           ,[ToUserId]
           ,[CreateDate]
           ,[CreateDateStamp]
           ,[IsRead]
           ,[Status])
     VALUES
           (@RoomId
           ,@Message
           ,@FromUserId
           ,@ToUserId
           ,@CreateDate
           ,@CreateDateStamp
           ,@IsRead
           ,@Status)
     SELECT @Id = @@IDENTITY
END
GO

CREATE PROC ChatMessage_UpdateIsRead
@ChatRoomId INT,
@ToUserId INT
AS
BEGIN
	UPDATE ChatMessages SET IsRead = 1 WHERE @ChatRoomId = RoomId AND ToUserId = @ToUserId
END
GO

CREATE PROC ChatMessages_CountUnreadMessage
@ChatRoomId INT,
@ToUserId INT
AS
BEGIN
	SELECT COUNT(*) FROM ChatMessages WHERE RoomId = @ChatRoomId AND ToUserId = @ToUserId
END
GO

CREATE PROC Users_Search
@UserName VARCHAR(100),
@FullName NVARCHAR(200),
@DepartmentId INT
AS
BEGIN
	DECLARE @sql NVARCHAR(MAX) = ''
	DECLARE @where NVARCHAR(MAX) = ' WHERE 1 = 1 '
	SET @sql += 'SELECT * FROM Users '
	IF 	@DepartmentId != 0
	BEGIN
		SET @where += ' AND DepartmentId = ' + @DepartmentId
	END
	
	IF @UserName IS NOT NULL
	BEGIN
		SET @where += ' AND LOWER(UserName) Like ''%' + @UserName + '%'''
	END
	
	IF @FullName IS NOT NULL
	BEGIN
		SET @where += ' AND LOWER(FirstName + LastName) Like ''%' + @FullName + '%'''
	END
	
	EXEC (@sql + @where)
	
END
GO